using System;
using System.Collections.Generic;
using System.Text;
using LearningEnglishProgram.DTO;
using System.Data.SQLite;

namespace LearningEnglishProgram.DAO
{
    class Scripts
    {
        static public String INSERT_WORDS(WordDTO wordDTO)
        {
            if(wordDTO == null)
                return null;

            StringBuilder sResBuilder = new StringBuilder();
            
            String sInsertWordsTable = @"BEGIN;

                                        -- Create in-memory temp table for variables
                                        PRAGMA temp_store = 2;
                                        DROP TABLE IF EXISTS _Variables;
                                        CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, IntVal INTEGER);

                                        -- Declaring a variable
                                        INSERT INTO _Variables (Name) 
                                        VALUES ('VarName');



                                        INSERT INTO WORDS (WORD)
                                        VALUES ('{0}');



                                        -- Assigning a variable (pick the right storage class)
                                        UPDATE _Variables
                                        SET IntVal = last_insert_rowid() 
                                        WHERE Name = 'VarName';";

            sInsertWordsTable = String.Format(sInsertWordsTable, wordDTO.WORD);
            
            List<WordPropertyDTO> lstWordProperties = wordDTO.arrWordProperties;
            if(lstWordProperties == null)
                return null;
            
            sResBuilder.Append(sInsertWordsTable);
            for (int nItemIndex = 0; nItemIndex < lstWordProperties.Count; nItemIndex++)
            {
                WordPropertyDTO wordPropertiesDTO = lstWordProperties[nItemIndex];
                String sInsertWordPropertiesTable = @"INSERT INTO WORD_PROPERTIES (WORD_ID , 
						                                                           PRONUNCIATION , 
						                                                           WORD_FORM , 
						                                                           PHONETIC , 
						                                                           PREPOSITION , 
						                                                           VIETNAMESE_MEANING , 
						                                                           ENGLISH_MEANING , 
						                                                           EXAMPLE)
                                                       VALUES ( ( SELECT IntVal FROM _Variables WHERE Name = 'VarName' LIMIT 1 ) , 
	                                                              '{0}' , 
	                                                              '{1}' , 
	                                                              '{2}' , 
	                                                              '{3}' , 
	                                                              '{4}' , 
	                                                              '{5}' , 
	                                                              '{6}');";

                String[] arrParams = { wordPropertiesDTO.PRONUNCIATION , 
                                        wordPropertiesDTO.WORD_FORM , 
                                        wordPropertiesDTO.PHONETIC , 
                                        wordPropertiesDTO.PREPOSITION , 
                                        wordPropertiesDTO.VIETNAMESE_MEANING , 
                                        wordPropertiesDTO.ENGLISH_MEANING , 
                                        wordPropertiesDTO.EXAMPLE
                                      };

                sInsertWordPropertiesTable = String.Format(sInsertWordPropertiesTable, arrParams);
                sResBuilder.Append(sInsertWordPropertiesTable);

            } // end for

            sResBuilder.Append("END;");

            return sResBuilder.ToString();
        }
    }
}
